﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SQLServerHealthCheck.Model;
using AG.Utilities;
using System.Data.SqlClient;


namespace SQLServerHealthCheck.SQLServer.AutoFixes
{
    public class ServiceFixXPcmdshellEnabled : AutoFixBase
    {

        public ServiceFixXPcmdshellEnabled()
        {
            IssueTypeId = 2006;
        }

        public override void Fix(List<Issue> issues)
        {
            try
            {

                HealthCheckDataContext dc = new HealthCheckDataContext(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringSSHCDatabase"]);



                var Qry = from i in issues
                          from ss in dc.Issues
                          from ser in dc.Services
                          where ss.IssueTypeId == IssueTypeId
                          && i.IssueId == ss.IssueId
                          && ss.TargetId == ser.ServiceID
                          select  new { cns =ser.GetConnectionString("master"), IssueId = ss.IssueId };
              
                foreach (var item in Qry)
                {
                    string connectionString;

                    connectionString = item.cns;

                  
                    using (SqlConnection cn = new SqlConnection(connectionString))
                    {

                        string query = @"   declare
                                        @oldvalue int;
                                        set @oldvalue =  CONVERT(int, (select value from   sys.configurations
                                        where name = 'show advanced options'));
                                        EXEC sp_configure 'show advanced options', 1
                                        RECONFIGURE
                                        EXEC sp_configure 'xp_cmdshell', 0;
                                        RECONFIGURE
                                        EXEC sp_configure 'show advanced options', @oldvalue
                                        RECONFIGURE
                                        ";
                        SqlCommand cmd = new SqlCommand(query, cn);
                        cmd.Connection.Open();
                        cmd.ExecuteScalar();
                        Issue issue = dc.Issues.SingleOrDefault(i => i.IssueId == item.IssueId);
                        issue.Fixed = true;
                  
                        dc.SubmitChanges();
                    }
                }

            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }

        }

    }
}
